package com.service.db.impl;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.MessageFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.concurrent.LinkedBlockingQueue;

import com.service.bean.company.Company;
import com.service.bean.device.Vehicle;
import com.service.bean.oil.OilCard;
import com.service.bean.oil.OilChangeBean;
import com.service.bean.other.OfflineData;
import com.service.bean.other.User;
import com.service.cache.CacheManager;
import com.service.db.DbConnectionManager;
import com.service.db.PooledConnection;
import com.service.other.Logger;

public class DbOther {
	
	public List<User> getUsers() {
		PooledConnection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<User> beans = new ArrayList<User>();
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement("select aui.UserName,aui.Password from aspnet_UserInfo aui "
					+ " left outer join aspnet_UserRole aur on aui.UserId=aur.UserId "
					+ " left outer join aspnet_Role ar on aur.RoleId=ar.RoleId "
					+ " where ar.ShowMode='1'");
			rs = pstmt.executeQuery();
			while (rs.next()) {
				User bean = new User();
				bean.name = rs.getString("UserName");
				bean.password = rs.getString("Password");
				beans.add(bean);
			}
		} catch (Exception e) {
			Logger.err(e);
		} finally {
			try {
				if(rs!=null && !rs.isClosed()){
					rs.close();
				}
				if(pstmt!=null && !pstmt.isClosed()){
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (Exception e) {
				Logger.err(e);
			}
		}
		return beans;
	}
	
	public void updateUsers(List<User> users) {
		if(users==null || users.size()==0){
			return;
		}
		PooledConnection con = null;
		Statement pstmt = null;
		try {
			con = DbConnectionManager.getConnection();
			boolean autoCommit = con.getAutoCommit();
			con.setAutoCommit(false);
			pstmt = con.prepareStatement();
			// 使用Statement同时收集多条sql语句
			for (User u : users) {
				String strSQL = MessageFormat
						.format("update aspnet_UserInfo set Password = {0} where UserName = {1} ;",
								"'" + String.valueOf(u.password) + "'",
								"'" + String.valueOf(u.name) + "'");
				pstmt.addBatch(strSQL);
			}
			pstmt.executeBatch();
			con.commit();
			con.setAutoCommit(autoCommit);
		} catch (Exception e) {
			Logger.err(e);
		}finally{
			try {
				if(pstmt!=null && !pstmt.isClosed()){
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (Exception e) {
				Logger.err(e);
			}
		}
	}
	
	//获取所有车辆当天的加油量
	public static double getCountOil(String equipmentId){
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		PooledConnection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs= null;
		double countOil =0;
		try {
			String sql ="select SUM(changeamt) count from crc_OilChange where EquipmentId =? and  convert(varchar(10),ChangeTimeFrom,23) = ?";
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, equipmentId);
			pstmt.setString(2, sdf.format(new Date()));
			rs = pstmt.executeQuery();
			while(rs.next()){
				countOil =rs.getDouble("count");
			}
		}catch(Exception e){
			Logger.err(e);
		}finally{
			try{
				if(rs!=null && !rs.isClosed()){
					rs.close();
				}
				if(pstmt!=null && !pstmt.isClosed()){
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			}catch(Exception e){
				Logger.err(e);
			}
		}
		return countOil;
	}
		
	/**
	 * 保存油量变化
	 * @param bean 油量变化对象
	 */
	public void insert(Company company) {
		if (CacheManager.instance().getOilChangeBeanSaveQueue(company).size()==0) {
			return;
		}
		PooledConnection con = null;
		Statement pstmt = null;
		try {
			int x = 0;
			boolean isEnd = false;
			con = DbConnectionManager.getConnection(company);
			con.setAutoCommit(false); 
			pstmt = con.prepareStatement();
			String sql = "insert into crc_OilChange "
					+ "(ChangeType,CompanyCode,ChangeDate,OilCardNO,ChangeTimeFrom,ChangeTimeTo,"
					+ "ChangeOilFrom,ChangeOilTo,EquipmentId,ProjectId,ChangeAmt,OilAmt,OilCardAmt,"
					+ "PayMode,Remark,Status,OilUnitPrice,OilPrice)"
					+ " values ";
			do{
				OilChangeBean bean = CacheManager.instance().getOilChangeBeanSaveQueue(company).poll();
				pstmt.addBatch(sql+MessageFormat.format("({0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13},{14},{15},{16},{17})",bean.getAll()));
				isEnd = CacheManager.instance().getOilChangeBeanSaveQueue(company).isEmpty();
				x++;
				if(x==1000||isEnd){
					long y = System.currentTimeMillis();
					pstmt.executeBatch();
					con.commit();
					pstmt.clearBatch();
					long z = System.currentTimeMillis();
					Logger.println("[" + company.companyName + "]成功存储"+x+"条crc_OilChange数据,用时"+(z-y)+"毫秒.");
					System.out.println("["+company.companyName+"]管道中剩余"+CacheManager.instance().getOilChangeBeanSaveQueue(company).size()+"条数据");
					x=0;
				}
			} while (!isEnd);
			con.setAutoCommit(true);
		} catch (Exception e) {
			Logger.err(e);
		} finally{
			try{
				pstmt.close();
				con.close();
			}catch(Exception e){
				Logger.err(e);
			}
		}
	}
		
	/**
	 * 更新油卡中剩余油量
	 * @param company
	 */
	public void updateOilCard(Company company) {
		if (CacheManager.instance().getUpdateOilCardQueue(company).size()==0) {
			return;
		}
		PooledConnection con = null;
		Statement pstmt = null;
		try {
			int x = 0;
			boolean isEnd = false;
			con = DbConnectionManager.getConnection(company);
			con.setAutoCommit(false); 
			pstmt = con.prepareStatement();
			do{
				OilCard card = CacheManager.instance().getUpdateOilCardQueue(company).poll();
				pstmt.addBatch("update crc_OilCard set LeftAmt='"+card.oilLeftAmt+"' where OilCardId='"+card.oilCardId+"'");
				isEnd = CacheManager.instance().getUpdateOilCardQueue(company).isEmpty();
				x++;
				if(x==1000||isEnd){
					long y = System.currentTimeMillis();
					pstmt.executeBatch();
					con.commit();
					pstmt.clearBatch();
					long z = System.currentTimeMillis();
					Logger.println("[" + company.companyName + "]成功更新"+x+"条crc_OilCard数据,用时"+(z-y)+"毫秒.");
					System.out.println("["+company.companyName+"]管道中剩余"+CacheManager.instance().getUpdateOilCardQueue(company).size()+"条数据");
					x=0;
				}
			} while (!isEnd);
			con.setAutoCommit(true);
		} catch (Exception e) {
			Logger.err(e);
		}finally{
			try{
				if(pstmt!=null && !pstmt.isClosed()){
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			}catch(Exception e){
				Logger.err(e);
			}
		}
	}
	
	/**
	 * 根据车辆查找车辆所属项目
	 * @param vehicle
	 * @return
	 */
	public String getProjectIdByVehicle(Vehicle vehicle) {
		PooledConnection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "SELECT ProjectId FROM crc_EquipmentProject WHERE EquipmentId = ?";
		String projectId = null;
		try {
			con = DbConnectionManager.getConnection(vehicle.company);
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, vehicle.equipmentId);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				projectId = String.valueOf(rs.getObject("ProjectId"));
				break;
			}
		} catch (Exception e) {
			Logger.err(e);
		} finally {
			try {
				if (null != rs)
					rs.close();
				if (null != pstmt)
					pstmt.close();
				if (null != con)
					con.close();
			} catch (Exception ex) {
				Logger.err(ex);
			}
		}
		return projectId;
	}
	
	/**
	 * 批量保存或更新车辆离线数据
	 * @param company
	 */
	public void batchSaveOfflineData(Map<Company, LinkedBlockingQueue<OfflineData>> dataMap) {
		PooledConnection conn = null;
		CallableStatement cs = null;
		OfflineData data = null;
		String sql = "{call dbo.proc_save_offlinedata(?,?,?,?) }";
		List<Company> companylist = new ArrayList<Company>();
		try {
			for (Map.Entry<Company, LinkedBlockingQueue<OfflineData>> entry : dataMap.entrySet()) {
				long x = System.currentTimeMillis();
				conn = DbConnectionManager.getConnection(entry.getKey());
				companylist.add(entry.getKey());
				conn.setAutoCommit(false);
				cs = conn.prepareCall(sql);
				
				boolean isEnd = false;
				int i = 0;
				
				do {
					data = entry.getValue().poll();
					cs.setString(1, data.equipmentId);
					cs.setTimestamp(2, new Timestamp(data.lastOnlineTime.getTime()));
					cs.setTimestamp(3, new Timestamp(data.offlineTime.getTime()));
					cs.setInt(4, data.isInsert);
					cs.addBatch();
					isEnd = entry.getValue().isEmpty();
					i++;
					if (isEnd || i == 1000) {
						cs.executeBatch();
						conn.commit();
						cs.clearParameters();
					}
				} while (!isEnd);
				
				long y = System.currentTimeMillis();
				Logger.println("成功存储" + i + "条offline数据,耗时 " + (y - x) + " 毫秒");
				
			}
		} catch (Exception e) {
			Logger.err(e);
		} finally {
			try {
				if (null != cs)
					cs.close();
				if (null != conn)
					conn.close();
			} catch (Exception ex) {
				Logger.err(ex);
			}
		}
	}
	
}
